package Interface.Producao.Cortesias;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sft.BancodeDados.CriaConexao;
import sft.Logica.Cortesias;

public class CortesiasDao {
    
    private Connection conexao;

    public CortesiasDao() throws SQLException {
        this.conexao = new CriaConexao().getConnection();
    }
    
    public void adicionar(List<Cortesias> c) throws SQLException{
        try (PreparedStatement stmt = conexao.prepareStatement("INSERT INTO `sft`.`sft_cortesias` ("
                     + "cort_dt_data, cort_nm_nome, cort_vl_valor, cort_mt_motivo, cort_at_autorizacao, cort_os_oscort) " +
     "	VALUES (?, ?, ?, ?, ?, ?)")) {
            for (int i = 0; i < c.size(); i++) {
                stmt.setString(1, c.get(i).getData());
                stmt.setString(2, c.get(i).getNome());
                stmt.setDouble(3, c.get(i).getValor());
                stmt.setString(4, c.get(i).getMotivo());
                stmt.setString(5, c.get(i).getAutorizacao());
                stmt.setString(6, c.get(i).getOs());
                
                stmt.execute();
                stmt.clearParameters();
            }
            stmt.close();
        }
    
    }
    
    public void clearTabela() throws SQLException{
        
        try (PreparedStatement stmt = conexao.prepareStatement("TRUNCATE TABLE `sft`.`sft_cortesias`")) {
            stmt.execute();
        }
    }
    
    public List<Cortesias> getCortesias() throws SQLException{
        PreparedStatement stmt = conexao.prepareStatement("select * from `sft`.`sft_cortesias`");
        ResultSet rs = stmt.executeQuery();
        
        List<Cortesias> list = new ArrayList<>();
        
        while (rs.next()) {
            Cortesias c = new Cortesias();
            c.setData(rs.getString(2));
            c.setNome(rs.getString(3));
            c.setValor(rs.getDouble(4));
            c.setMotivo(rs.getString(5));
            c.setAutorizacao(rs.getString(6));
            c.setOs(rs.getString(7));
            list.add(c);
        }
               
        return list;
    }

    public List<Cortesias> getCortesias(String text) throws SQLException {
        PreparedStatement stmt = conexao.prepareStatement("select * from sft_cortesias c where c.cort_os_oscort like ?");
        stmt.setString(1, "%" + text + "%");
        ResultSet rs = stmt.executeQuery();
        
        List<Cortesias> list = new ArrayList<>();
        
        while (rs.next()) {
            Cortesias c = new Cortesias();
            c.setData(rs.getString(2));
            c.setNome(rs.getString(3));
            c.setValor(rs.getDouble(4));
            c.setMotivo(rs.getString(5));
            c.setAutorizacao(rs.getString(6));
            c.setOs(rs.getString(7));
            list.add(c);
        }
               
        return list;
    }
}
